package dataBase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

	/**
	 * The class contains all the tables used in the MySql database
	 */
public class DBInitializer {
	
	private static DBInitializer instance = new DBInitializer();
	final static String driver = "com.mysql.jdbc.Driver";
	Connection con = null;
	Statement stmt = null;


/******************************************************************************
 *                     user related database tables                           *                  
 ******************************************************************************/
	Map<String,String> userPasswordMap = null;
	ISqlTable userPasswordTable = null;
	
	Map<String,String> userInfoMap = null;
	ISqlTable userInfoTable = null;
	
/******************************************************************************
 *                     code related database tables                           *                  
 ******************************************************************************/
	Map<String,String> categoriesMap = null;
	ISqlTable categoriesTable = null;
	
	Map<String,String> codeCategoriesMap = null;
	ISqlTable codeCategoriesTable = null;
	
	Map<String,String> codeMap = null;
	ISqlTable codeTable = null;
	
	Map<String,String> codeTitleMap = null;
	ISqlTable codeTitleTable = null;
/******************************************************************************/

	private DBInitializer(){
		// Singleton pattern
		userPasswordMap = new HashMap<String, String>();
		userPasswordMap.put("UID", "TEXT");
		userPasswordMap.put("Password", "TEXT");
		
		userInfoMap = new HashMap<String, String>();
		userInfoMap.put("UID", "TEXT");
		userInfoMap.put("email", "TEXT");
		userInfoMap.put("admin", "ENUM('true', 'false')");

		categoriesMap = new HashMap<String, String>();
		categoriesMap.put("category", "TEXT");
		
		codeCategoriesMap = new HashMap<String, String>();
		codeCategoriesMap.put("CID", "TEXT");
		codeCategoriesMap.put("category", "TEXT");

		codeMap = new HashMap<String, String>();
		codeMap.put("CID", "TEXT");
		codeMap.put("Version", "TEXT");
		codeMap.put("UID", "TEXT");
		codeMap.put("Body", "LONGTEXT"); // 
		codeMap.put("Visibility", "TEXT");

		codeTitleMap = new HashMap<String, String>();
		codeTitleMap.put("CID", "TEXT");
		codeTitleMap.put("Title", "TEXT");
}
	public static DBInitializer getInstance(){
		return instance;
	}
	
	public boolean initDB() throws ClassNotFoundException, SQLException{
		setUp();
		return true;
		
	}
	
	// TODO: select right engine
	/**
	 * Creates all the database tables in the MySql database
	 * @return true if all tables were created successfully otherwise false
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public boolean setUp() throws ClassNotFoundException, SQLException{
		String url = "jdbc:mysql://localhost:3306/";
		// Load the Driver class. 
		Class.forName(driver);
		con = DriverManager.getConnection (url+ "CodeSharingDB", "root", "skravi");
		stmt = con.createStatement();
		
		List<String> usersTablesPK = new LinkedList<String>();
		usersTablesPK.add("UID(128)");
		userPasswordTable = new UserPasswordSqlTable(stmt, "UserPassword", userPasswordMap, "innodb");
		userPasswordTable.createTable(usersTablesPK);

		userInfoTable = new SqlTable(stmt, "UserInfo", userInfoMap, "innodb");
		userInfoTable.createTable(usersTablesPK);

		List<String> categoriesPK = new LinkedList<String>();
		categoriesPK.add("Category(256)");
		categoriesTable = new SqlTable(stmt, "Categories", categoriesMap, "innodb");
		categoriesTable.createTable(categoriesPK);

		codeCategoriesTable = new SqlTable(stmt, "CodeCategories", codeCategoriesMap, "innodb");
		codeCategoriesTable.createTable();// no Primary key for this table

		List<String> codesPK = new LinkedList<String>();
		codesPK.add("CID(128)");
		codesPK.add("Version(16)");
		codeTable = new SqlTable(stmt, "Codes", codeMap, "innodb");
		codeTable.createTable(codesPK);

		List<String> codeTitlePK = new LinkedList<String>();
		codeTitlePK.add("CID(128)");
		codeTitleTable = new SqlTable(stmt, "CodeTitles", codeTitleMap, "innodb");
		codeTitleTable.createTable(codeTitlePK);
		
		return true;
	}

	public Statement getStmt() {
		return stmt;
	}

	public ISqlTable getUserPasswordTable() {
		return userPasswordTable;
	}
	public ISqlTable getUserInfoTable() {
		return userInfoTable;
	}
	public ISqlTable getCategoriesTable() {
		return categoriesTable;
	}
	public ISqlTable getCodeCategoriesTable() {
		return codeCategoriesTable;
	}
	public ISqlTable getCodeTable() {
		return codeTable;
	}
	public ISqlTable getCodeTitleTable() {
		return codeTitleTable;
	}
	// connection will be reopened on a new session
	public void tearDown(){
		try{
			stmt.close();
			con.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
}
